Customer Case Study · HR · People Analytics & Data Modelling
The power transmission company's HR numbers lived in separate extracts stitched together with VLOOKUPs. HMC connected them in one Power BI data model, moved every Excel calculation into Power Query (M) and DAX, and built a 12-page dashboard where every chart filters every other.
Counts from the Power BI file shown below.
Headcount, CTC, joining and relieving data came from different sources. Every monthly review meant rebuilding the joins in Excel, and every chart was a static picture of one cut.
HRMS extracts, CTC sheets, compa data and joining and relieving trackers, each with its own keys and layout.
Dozens of lookup columns copied down every row: slow to recalculate and broken by a single inserted column.
Charts did not talk to each other. Answering "what about Finance, M4 and above?" meant a new pivot.
Age bands, tenure groups, fiscal months and attrition were recalculated by hand each cycle.
Built with the power transmission company's HR team, so the model matches how they already think about people.
Select a step to see what happened.
Traced the Excel working end to end.
Deliverable: Source and lookup inventory.
One fact table, many small dimensions.
Deliverable: Model diagram and relationship plan.
Every Excel step rebuilt where it belongs.
Deliverable: 12-page Power BI report.
The dashboard replaced the Excel pack.
Deliverable: Refresh guide and walkthrough.
New analysis plugs into the same model.
Deliverable: Extensible model.
The original Power BI file first, then three working modules: explore the model, see the Excel working rebuilt in M and DAX, and use a cross-filtering dashboard built the same way.
Shown as delivered. Click any screen to enlarge it.
HMC_Dynamic Month _ Active Joining finds the latest data month and converts it to a fiscal month number.Switch between the old Excel working and the Power BI model. Click any table to see its fields and relationship, then pick a value to watch the filter flow into Fact_Report.
Tables and fields from the model view; row counts from a synthetic workforce calibrated to the report's totals.
Pick a metric to compare the Excel formula with its DAX measure, step through the fiscal-month measure from the report, or watch Power Query shape a raw extract.
A working replica of the report on a synthetic workforce of 856 employees calibrated to the original totals. Click a bar, slice or icon to cross-filter every other visual; use the slicers or the filter chips to reset.
Synthetic employees; headline totals match the report (attrition within 0.05 pt). Cost series as read from the report's trend chart. Function names beyond the eight visible in the original, and exit reasons, are illustrative.
The monthly HR pack now refreshes from source, and follow-up questions are answered with a click instead of a new pivot.
By task, Excel working vs Power BI model
Calculation objects by type
Figures indicative; validated metrics available on request.
A relationship replaces a VLOOKUP column on every row, and it never breaks when a column moves.
Shape data in M, calculate in DAX, and leave Excel as a source, not a calculator.
Cross-filtering comes free once every chart sits on the same model.